import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from datetime import datetime

date_today = '2025_03_17'
# 定义文件路径
excel1_path = f'C:\\Users\\22954\\Desktop\\file\\{date_today}\\SKU拆分.xlsx'
excel2_path = f'C:\\Users\\22954\\Desktop\\file\\{date_today}\\备货&调整库存总表-2025(1).xlsx'
output_path = f'C:\\Users\\22954\\Desktop\\file\\{date_today}\\比对完成结果.xlsx'

# 读取Excel文件
excel1_df = pd.read_excel(excel1_path)
excel2_df = pd.read_excel(excel2_path)

# 提取需要比对的列
excel1_data = excel1_df.iloc[:, 1:3].dropna()  # B列和C列
excel2_data = excel2_df.iloc[:, 0:2].dropna()  # A列和B列

# 将数据转换为元组集合，方便比对
excel1_tuples = set(excel1_data.itertuples(index=False, name=None))
excel2_tuples = set(excel2_data.itertuples(index=False, name=None))

# 找到重复的内容（与备货表比对）
duplicate_tuples_external = excel1_tuples.intersection(excel2_tuples)

# 找到自身文件内的重复内容
duplicate_tuples_internal = set()
seen_tuples = set()
for t in excel1_tuples:
    if t in seen_tuples:
        duplicate_tuples_internal.add(t)
    else:
        seen_tuples.add(t)

# 合并所有重复的内容
all_duplicate_tuples = duplicate_tuples_external.union(duplicate_tuples_internal)

# 创建一个新的Excel文件
new_wb = Workbook()
sheet1 = new_wb.active
sheet1.title = "重复标记"
sheet2 = new_wb.create_sheet("已处理不重复")

# 定义黄色填充
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 处理excel1的数据
for row in excel1_df.itertuples(index=False):
    row_data = list(row)
    # 写入Sheet1（所有内容）
    sheet1.append(row_data)
    # 检查B列和C列是否在重复集合中
    if (row[1], row[2]) in all_duplicate_tuples:
        # 如果重复，标记为黄色
        for cell in sheet1[sheet1.max_row]:
            cell.fill = yellow_fill

# 处理不重复的内容
unique_data = excel1_df[~excel1_df.apply(lambda x: (x[1], x[2]) in all_duplicate_tuples, axis=1)]
for row in unique_data.itertuples(index=False):
    sheet2.append([row[1], row[2], datetime.now().strftime("%m-%d")])  # B列和C列写入A列和B列，C列为日期

# 保存新的Excel文件
new_wb.save(output_path)

print(f"处理完成，结果已保存到 {output_path}")